package com.yumo.kangchenjunga.coupon.v1;

import java.util.List;
import java.util.Optional;

import javax.persistence.LockModeType;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Lock;
import org.springframework.data.jpa.repository.Query;

public interface CouponCustomerAllocationRepository extends JpaRepository<CouponCustomerAllocationEntity, Integer> {

	@Query(nativeQuery = true, value = "" +
			"select" +
			"    a.customer_id as customerId," +
			"    a.activity_id as activityId," +
			"    count(if(a.status in ('ALLOCATED', 'OBTAINED'), 1, null)) as coupons," +
			"    count(if(o.status in ('OBTAINED', 'USED'), 1, null)) as obtainedCoupons," +
			"    count(if(o.status = 'USED', 1, null)) as usedCoupons" +
			"  from coupon_customer_allocation a left join coupon_customer_obtainment o using (coupon_id)" +
			"  where a.customer_id = ?1" +
			"  group by a.activity_id")
	List<CouponCustomerActivitySummaryProjection> summaryByCustomerId(int customerId);

	@Lock(LockModeType.PESSIMISTIC_WRITE)
	Optional<CouponCustomerAllocationEntity> findFirstByCustomerIdAndActivityIdAndStatusOrderById(int customerId,
			int activityId, CouponStatus status);

	Page<CouponCustomerAllocationEntity> findByStatus(CouponStatus status, Pageable pageable);

}
